SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

/******************************************************************************/
/* Step 1. Remove stored procedure if it already exists                       */
/******************************************************************************/
IF (SELECT OBJECT_ID('[Kernel].[ServiceMemberWith]')) IS NOT NULL
BEGIN
  DROP PROCEDURE [Kernel].[ServiceMemberWith]
END

GO

/*****************************************************************************/
/* Stored Procedure :: ServiceMemberWith                                     */
/*****************************************************************************/
/* Copyright (C) 2007, Perot Systems Corporation. All right reserved.        */
/* Developer: Ed. Mueller (edward.mueller@ps.net)                            */
/* Started:  10/13/2007                                                      */
/*****************************************************************************/
CREATE PROCEDURE [Kernel].[ServiceMemberWith]( @ServiceMemberID BIGINT )
AS  
BEGIN

SELECT 
		sm.OID as 'ServiceMenOID', 
		sm.Version as 'ServiceMenVersion', 
		sm.PrimaryAddressID as 'ServiceMenPrimaryAddressID', 
		sm.PermanentAddressID as 'ServiceMenPermanentAddressID',

		People.OID as 'PeopleOID', 
		People.Version as 'PeopleVersion', 
		People.FirstName, 
		People.LastName, 
		People.MiddleInitial, 
		People.DOB, 
		People.Sex, 
		People.Phone1, 
		People.Phone2, 
		People.eMailAddress,

		sh.OID			as 'ServiceHistoryOID',
		sh.Version		as 'ServiceHistoryVersion',
		sh.StartDate,
		sh.EndDate,
		sh.UnitAssignment,

		sh.TypeOfDischargeID,
		dis.Description as 'DischargeType',

		sh.BranchOfServiceID,
		br.Version as 'BranchVersion',
		br.Branch,

		sh.RankID,
		mr.Version as 'RankVersion',
		mr.RankStructure,
		mr.Rank,
		mr.PayGrade as 'RankPayGrade',
		mr.Abbreviation as 'RankAbbreviation'
	FROM ServiceMen sm
	INNER JOIN People On sm.OID = People.OID
    LEFT OUTER JOIN dbo.ServiceHistory sh ON sm.OID = sh.PersonnelID
    AND sh.Version = (SELECT MAX(Version) FROM dbo.ServiceHistory
					WHERE PersonnelID = sm.OID)
    LEFT OUTER JOIN dbo.TypesOfMilitaryBranch br ON sh.BranchOfServiceID = br.OID
    LEFT OUTER JOIN dbo.MilitaryRanks mr ON sh.RankID = mr.OID
    LEFT OUTER JOIN dbo.TypesOfDischarge dis ON sh.TypeOfDischargeID = dis.OID
	WHERE (sm.OID = @ServiceMemberID)
END 
GO

/******************************************************************************/
/* Step 3. Assign appropriate permissions                                     */
/******************************************************************************/
GRANT EXECUTE ON [Kernel].[ServiceMemberWith] TO [Liberty_Users]
GO
-- Developers and Admins have permissions to execute through membership   